Hashtag (#) in the code chucks contain notes or additional code that can be run if the # is removed.
This script can take several files. - 1 file with the RFU measurements and metadata in plate format - An assay sheet file which contains the meta data that needs to be combined with the RFU measurement (only relevant if metadata is not in the RFU measurement file).
Example data: plate_to_df_example.xlsx, contains some meta data, additional data is added with sample sheets.
#install.packages("readxl")
library(readxl)
library(tidyverse)
library(readr)
library(writexl)
library(sessioninfo)Define input file for the RFU + meta data
setwd("...")
file <- "Plate_to_df_example.xlsx"
datasets <- tribble(
~data_range, ~meta1_range, ~meta2_range, ~meta3_range, ~Plate,
"A2:M10", "A2:M10", "A2:M10", "A2:M10", "Plate_1",
"A14:M22", "A14:M22", "A14:M22", "A14:M22", "Plate_2",
"A26:M34", "A26:M34", "A26:M34", "A26:M34", "Plate_3",
"A38:M46", "A38:M46", "A38:M46", "A38:M46", "Plate_4",
"A50:M58", "A50:M58", "A50:M58", "A50:M58", "Plate_5",
"A62:M70", "A62:M70", "A62:M70", "A62:M70", "Plate_6"
)This function:
This approach work for multiple plate setups. If more than 6 plates, add the ranges to the datasets range above. If less, just leave them empty and they will not be in the dataset in the end.
load_plate <- function(file, data_range, meta1_range, meta2_range,meta3_range, plate_id) {
# Load signal
data <- suppressMessages(read_excel(file, range = data_range, sheet = "Data") %>%
rename(sample = 1) %>%
pivot_longer(-sample,
names_to = "Col",
values_to = "Signal"))
# Metadata 1
meta1 <- suppressMessages(read_excel(file, range = meta1_range, sheet = "Meta_data1") %>%
rename(sample = 1) %>%
mutate(across(everything(), as.character)) %>%
pivot_longer(-sample,
names_to = "Col",
values_to = "Concentration")) #Change to meta_data1 description
# Metadata 2
meta2 <- suppressMessages(read_excel(file, range = meta2_range, sheet = "Meta_data2") %>%
rename(sample = 1) %>%
mutate(across(everything(), as.character)) %>%
pivot_longer(-sample,
names_to = "Col",
values_to = "Sample_type")) #Change to meta_data2 description
# Metadata 3
meta3 <- suppressMessages(read_excel(file, range = meta3_range, sheet = "Meta_data3") %>%
rename(sample = 1) %>%
mutate(across(everything(), as.character)) %>%
pivot_longer(-sample,
names_to = "Col",
values_to = "xxx")) #Change to meta_data3 description
# Merge all
full <- data %>%
left_join(meta1, by = c("sample", "Col")) %>%
left_join(meta2, by = c("sample", "Col")) %>%
left_join(meta3, by = c("sample", "Col")) %>%
mutate(Plate = plate_id,
Col = as.integer(Col),
Well_ID = paste0(sample, sprintf("%02d", Col))) %>%
dplyr::select(-sample, -Col)
return(full)
}This step applies the function load_plate() to each plate and returns a long dataframe with all data.
Combined <- datasets %>%
mutate(data = pmap(
list(data_range, meta1_range, meta2_range,meta3_range, Plate),
~ load_plate(file, ..1, ..2, ..3, ..4, ..5)
)) %>%
pull(data) %>%
bind_rows()%>%
filter(!is.na(Signal))Save the Combined dataframe here if all the metadata was in the RFU measurement file. If not continue and combine it with information from assay sheets.
Save the processed data in your favorite format
#write_csv(Combined, "Step6_assays_RFU_only.csv")
#write_xlsx(Combined, "final_plate_data.xlsx")sheet1 <- read_xlsx("Sheet1.xlsx", range = "B6:F216") %>% filter(!is.na(RNA), RNA != 0) %>% mutate(Assay = "sheet1")
sheet2 <- read_xlsx("Sheet2.xlsx", range = "B6:F216") %>% filter(!is.na(RNA), RNA != 0) %>% mutate(Assay = "sheet2")
sheet3 <- read_xlsx("Sheet3.xlsx", range = "B6:F216") %>% filter(!is.na(RNA), RNA != 0) %>% mutate(Assay = "sheet3")
sheet4 <- read_xlsx("Sheet4.xlsx", range = "B6:F216") %>% filter(!is.na(RNA), RNA != 0) %>% mutate(Assay = "sheet4")
sheet5 <- read_xlsx("Sheet5.xlsx", range = "B6:F216") %>% filter(!is.na(RNA), RNA != 0) %>% mutate(Assay = "sheet5")
sheet6 <- read_xlsx("Sheet6.xlsx", range = "B6:F216") %>% filter(!is.na(RNA), RNA != 0) %>% mutate(Assay = "sheet6")
all_sheet <- rbind(sheet1,sheet2,sheet3,sheet4,sheet5,sheet6)
all_sheet_clean <- all_sheet %>%
mutate(
Plate_join = paste0("Plate_", Plate),
Well_join = str_replace(Well, "([A-H])(\\d{1})$", "\\10\\2") # A1 → A01
)final_df <- Combined %>%
left_join(
all_sheet_clean,
by = c("Plate" = "Plate_join", "Well_ID" = "Well_join")
) %>%
dplyr::select(-xxx, -Well, -Plate.y)Save the combined data file in your favorite format
#write_csv(final_df, "Step6_assays_combined.csv")
#write_xlsx(final_df, "final_plate_data.xlsx")# R studio Version
rstudioapi::versionInfo()$version## [1] '2026.7.1.147'# Session report
sessioninfo::session_info()## ─ Session info ───────────────────────────────────────────────────────────────
## setting value
## version R version 4.5.3 (2026-03-11 ucrt)
## os Windows 11 x64 (build 26200)
## system x86_64, mingw32
## ui RTerm
## language (EN)
## collate Danish_Denmark.utf8
## ctype Danish_Denmark.utf8
## tz Europe/Copenhagen
## date 2026-08-25
## pandoc 3.8.3 @ C:/Program Files/RStudio/resources/app/bin/quarto/bin/tools/ (via rmarkdown)
## quarto 1.9.38 @ C:\\PROGRA~1\\RStudio\\RESOUR~1\\app\\bin\\quarto\\bin\\quarto.exe
##
## ─ Packages ───────────────────────────────────────────────────────────────────
## package * version date (UTC) lib source
## bslib 0.11.0 2026-05-16 [1] CRAN (R 4.5.3)
## cachem 1.1.0 2024-05-16 [1] CRAN (R 4.5.3)
## cellranger 1.1.0 2016-07-27 [1] CRAN (R 4.5.3)
## cli 3.6.6 2026-04-09 [1] CRAN (R 4.5.3)
## digest 0.6.39 2025-11-19 [1] CRAN (R 4.5.3)
## dplyr * 1.2.1 2026-04-03 [1] CRAN (R 4.5.3)
## evaluate 1.0.5 2025-08-27 [1] CRAN (R 4.5.3)
## farver 2.1.2 2024-05-13 [1] CRAN (R 4.5.3)
## fastmap 1.2.0 2024-05-15 [1] CRAN (R 4.5.3)
## forcats * 1.0.1 2025-09-25 [1] CRAN (R 4.5.3)
## generics 0.1.4 2025-05-09 [1] CRAN (R 4.5.3)
## ggplot2 * 4.0.2 2026-02-03 [1] CRAN (R 4.5.3)
## glue 1.8.0 2024-09-30 [1] CRAN (R 4.5.3)
## gtable 0.3.6 2024-10-25 [1] CRAN (R 4.5.3)
## hms 1.1.4 2025-10-17 [1] CRAN (R 4.5.3)
## htmltools 0.5.9 2025-12-04 [1] CRAN (R 4.5.3)
## jquerylib 0.1.4 2021-04-26 [1] CRAN (R 4.5.3)
## jsonlite 2.0.0 2025-03-27 [1] CRAN (R 4.5.3)
## knitr 1.51 2025-12-20 [1] CRAN (R 4.5.3)
## lifecycle 1.0.5 2026-01-08 [1] CRAN (R 4.5.3)
## lubridate * 1.9.5 2026-02-04 [1] CRAN (R 4.5.3)
## magrittr 2.0.5 2026-04-04 [1] CRAN (R 4.5.3)
## otel 0.2.0 2025-08-29 [1] CRAN (R 4.5.3)
## pillar 1.11.1 2025-09-17 [1] CRAN (R 4.5.3)
## pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.5.3)
## purrr * 1.2.2 2026-04-10 [1] CRAN (R 4.5.3)
## R6 2.6.1 2025-02-15 [1] CRAN (R 4.5.3)
## RColorBrewer 1.1-3 2022-04-03 [1] CRAN (R 4.5.2)
## readr * 2.2.0 2026-02-19 [1] CRAN (R 4.5.3)
## readxl * 1.4.5 2025-03-07 [1] CRAN (R 4.5.3)
## rematch 2.0.0 2023-08-30 [1] CRAN (R 4.5.3)
## rlang 1.2.0 2026-04-06 [1] CRAN (R 4.5.3)
## rmarkdown 2.31 2026-03-26 [1] CRAN (R 4.5.3)
## rstudioapi 0.19.0 2026-06-11 [1] CRAN (R 4.5.3)
## S7 0.2.1 2025-11-14 [1] CRAN (R 4.5.3)
## sass 0.4.10 2025-04-11 [1] CRAN (R 4.5.3)
## scales 1.4.0 2025-04-24 [1] CRAN (R 4.5.3)
## sessioninfo * 1.2.4 2026-06-04 [1] CRAN (R 4.5.3)
## stringi 1.8.7 2025-03-27 [1] CRAN (R 4.5.2)
## stringr * 1.6.0 2025-11-04 [1] CRAN (R 4.5.3)
## tibble * 3.3.1 2026-01-11 [1] CRAN (R 4.5.3)
## tidyr * 1.3.2 2025-12-19 [1] CRAN (R 4.5.3)
## tidyselect 1.2.1 2024-03-11 [1] CRAN (R 4.5.3)
## tidyverse * 2.0.0 2023-02-22 [1] CRAN (R 4.5.3)
## timechange 0.4.0 2026-01-29 [1] CRAN (R 4.5.3)
## tzdb 0.5.0 2025-03-15 [1] CRAN (R 4.5.3)
## vctrs 0.7.3 2026-04-11 [1] CRAN (R 4.5.3)
## withr 3.0.3 2026-06-19 [1] CRAN (R 4.5.3)
## writexl * 1.5.4 2025-04-15 [1] CRAN (R 4.5.3)
## xfun 0.57 2026-03-20 [1] CRAN (R 4.5.3)
## yaml 2.3.12 2025-12-10 [1] CRAN (R 4.5.3)
##
## [1] C:/Users/wck955/AppData/Local/Programs/R/R-4.5.3/library
## * ── Packages attached to the search path.
##
## ──────────────────────────────────────────────────────────────────────────────